<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="mybatis.mappers.OrderMapper">
    <resultMap id="user" type="models.entities.UserEntity">
        <result property="email" column="email"/>
        <result property="password" column="password"/>
        <result property="name" column="userName"/>
        <result property="address" column="address"/>
        <result property="phoneNumber" column="phoneNumber"/>
        <result property="id" column="userId"/>
        <result property="sessionId" column="sessionId"/>
    </resultMap>
    <resultMap id="orderItem" type="models.entities.OrderItem">
        <result property="productId" column="productId"/>
        <result property="quantity" column="quantity"/>
        <result property="unitPrice" column="unitPrice"/>
        <result property="title" column="title"/>
    </resultMap>
    <resultMap id="orderEntity" type="models.entities.OrderEntity">
        <result property="id" column="orderId"/>
        <result property="userId" column="userId"/>
        <result property="placeDate" column="placeDate"/>
    </resultMap>
    <select id="getUser" resultMap="user">
        select * from user_profiles where userId = (select o.userId from order_items oi, orders o where o.orderId = #{orderId} and oi.orderId = o.orderId limit 1);
    </select>
    <insert id="placeOrder" parameterType="models.entities.OrderEntity">
        insert into orders (userId) values(#{userId});
        set @orderId := (select last_insert_id());
        insert into order_items(orderId, productId, quantity, unitPrice)
        select @orderId, productId, sum(quantity) as quantity, unitPrice from shopping_items
        where userId = #{userId}
        group by productId;
        delete from shopping_items where userId = #{userId};
        <selectKey keyProperty="id" resultType="int" order="AFTER">
            select @orderId as orderId;
        </selectKey>
    </insert>
    <select id="getOrderItems" resultMap="orderItem">
        select oi.*, p.title from order_items oi
        left join products p on p.productId = oi.productId
        where orderId = #{orderId};
    </select>
    <select id="getOrderListTotalCount" resultType="int">
        select count(*) from orders;
    </select>
    <select id="getOrderList" resultMap="orderEntity">
        select * from orders limit #{offset}, #{pageSize};
    </select>
    <select id="getUserOrders" resultMap="orderEntity">
        select * from orders where userId = #{userId};
    </select>
    <delete id="deleteOrder">
        delete from order_items where orderId = #{id};
        delete from orders where orderId = #{id};
    </delete>
</mapper>